--POUR CONTROLE

define erp='erp_mli2.';
define outils='outils_mli2.';

 --CALL uvse_ExistsOrCreate('NOM_ARTENT');

 DELETE FROM se_NOM_ARTENT;
/*
select * from (
SELECT &outils.stragg(source) source, combadv_id, unfab_cod, artent_ind, gene_cod, gene_ind, enreg_id, deb_dat, fin_dat, artent_lib, fit_cod, enreg_s
FROM (
  SELECT 'uv' source ,g.* FROM se_NOM_ARTENT g where UNFAB_COD ='ETUDE'
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.NOM_ARTENT g where UNFAB_COD ='ETUDE'
  )
where unfab_cod ='ETUDE'
GROUP BY combadv_id, unfab_cod, artent_ind, gene_cod, gene_ind, enreg_id, deb_dat, fin_dat, artent_lib, fit_cod, enreg_s
HAVING Count(1)!=2 ) r
ORDER BY gene_cod,gene_ind*/


/*nomenclatures de  de progres*/
 INSERT INTO se_NOM_ARTENT
 (SELECT Nvl((SELECT  combadv_id FROM &erp.vrt_combadv v WHERE COMBADV_TYP='S' AND v.GENE_COD=SubStr(p.prodbase,1,Length(p.prodbase)-1) AND v.gene_ind=Nvl(p.indice,0)),0) combadv_id,
         'ETUDE' unfab_cod,
         0 artent_ind,
         SubStr(p.prodbase,1,Length(p.prodbase)-1) gene_cod,
         Nvl(p.indice,0) gene_ind,
         0 enreg_id,
         To_Date('31/12/1967','dd/mm/yyyy')+Nvl(p.datap,0) deb_dat,
         NULL fin_dat,
         Nvl(p.des,'.') artent_lib,
         NULL fit_cod,
         'N' enreg_s
  FROM UV_PNOMENCLATURES noment, uv_pproduits p
  WHERE p.id=Nvl(noment.prcomplet,noment.id)
    AND EXISTS (SELECT * FROM UV_PNOMENCLATURES_NIVINF niv WHERE niv.id=noment.id)

 );

 ------- Lancer la génération des combadv sans variantes avec la requete suivante
 /*
     select gene_cod,gene_ind
      from erp_mli2.art_gene g
      where not exists (select 1 from erp_mli2.vrt_combadv v where v.combadv_typ='S' and v.gene_cod=g.gene_cod and v.gene_ind=g.gene_ind)
 */
 /*suppression des nomenclatures ou la combadv n'a pas \ufffdt\ufffd trouv\ufffde*/
 delete  FROM se_NOM_ARTENT WHERE combadv_id =0;

 UPDATE se_NOM_ARTENT tf SET enreg_id=(select enreg_id FROM &erp.NOM_ARTENT f WHERE f.combadv_id=tf.combadv_id AND f.unfab_cod=tf.unfab_cod AND f.ARTENT_IND=tf.ARTENT_IND ) ;
 UPDATE se_NOM_ARTENT tf SET enreg_s=Nvl((select enreg_s FROM &erp.NOM_ARTENT f WHERE f.combadv_id=tf.combadv_id AND f.unfab_cod=tf.unfab_cod AND f.ARTENT_IND=tf.ARTENT_IND ),'N') ;
 UPDATE se_NOM_ARTENT tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;





 CALL uvse_synchroverserp('combadv_id@unfab_cod@artent_ind','NOM_ARTENT','O') ;


 commit;


select * from (
SELECT &outils.stragg(source) source, combadv_id, unfab_cod, artent_ind, gene_cod, gene_ind, enreg_id, deb_dat, fin_dat, artent_lib, fit_cod, enreg_s
FROM (
  SELECT 'uv' source ,g.* FROM se_NOM_ARTENT g where UNFAB_COD ='ETUDE'
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.NOM_ARTENT g where UNFAB_COD ='ETUDE'
  )
where unfab_cod ='ETUDE'
GROUP BY combadv_id, unfab_cod, artent_ind, gene_cod, gene_ind, enreg_id, deb_dat, fin_dat, artent_lib, fit_cod, enreg_s
HAVING Count(1)!=2 ) r
ORDER BY gene_cod,gene_ind ;







